<?php
/**
* File holding the reporting and statistic class
*
* @author       Fabrice Douteaud <clearbudget@douteaud.com>
* @package      framework
* @access       public
*/

/***********************************************************************

  Copyright (C) 2008  Fabrice douteaud (clearbudget@douteaud.com)

    This file is part of ClearBudget.

    ClearBudget is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    ClearBudget is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with ClearBudget.  If not, see <http://www.gnu.org/licenses/>.


************************************************************************/

/**
* Main class to get transation data from the DB. This class handles the creation
* of pre-calculated data based on the DB raw data. It is optimized to reduce
* the round-trips to the DB.
*
* @author       Fabrice Douteaud <clearbudget@douteaud.com>
* @package      framework
* @access       public
*/
class class_report {
  /**
	* @var object Holds the instance of the current class
	* @access private
	* @static	
	*/
  private static $_instance = null;
  /**
	* @var object Holds an instance of the DB class
	* @access private	
	*/
  private $db = null;
  /**
	* @var object Holds an instance of the property keys class
	* @access private	
	*/
  private $keys = null;
  // processing fields  
  
  /**
	* @var string the month for which we want data for
	* @access public
	*/
  public $month = null;
  /**
	* @var string the year for which we want data for
	* @access private
	*/
  public $year = null;
  /**
	* @var string the month-year from which we want data
	* @access public
	*/
  public $limitMonth = null;
  /**
	* @var array Holds all the data pre-formatted from the DB
	* @access private
	*/
  private $stats = null;
  /**
	* @var array Associative array of category ID-Name pair
	* @access private
	*/
  private $categoryNames = null;
  /**
	* @var boolean flag to tell if data were loaded already
	* @access private
	*/
  private $gotData = false;
  /**
	* @var boolean flag to tell if valid data were loaded already
	* @access private
	*/
  public $validData = false;
  /**
	* @var array available report time range
	* @access private
	*/
  private $reportTimeRanges = array(3, 6, 12);
  
  /**
	* Magic function to return any data from the internal array of statistics 
	*	
	* 	
	* @param string the data name	
	* @return mixed the data value (array, string...)
	* @access public
	*/
  public function __get($name) {
    // set the return value to null by default
    $data = null;
    
    // check if the data is present and not null
    if(isset($this->stats[$name])) $data = $this->stats[$name];
    
    // filter the output as to get a clean value
    if(is_numeric($data)) $data = self::formatNumber($data);
    return $data;
    }

  /**
	* Gets all available report time range
	* 
	* Default values are already define at class level (3, 6, 12). This class adds dynamically the available years     
	*	
	* @return array of ranges
	* @access public
	*/
  public function getAvailableReportTimeRanges() {
    class_debug::addMsg(__FILE__, 'getting all distinct years with transactions', DEBUGDEBUG);
    // if not we get it from DB from the default range
    $this->db->addFields('year');
    $this->db->select('v_total_amount', false, 'distinct');
    $years = $this->db->fetchAllRows();
    foreach($years as $year) {
      class_debug::addMsg(__FILE__, 'Adding year '.$year['year'].' as available year', DEBUGDEBUG);
      $this->reportTimeRanges[] = $year['year'];
      }
    return $this->reportTimeRanges;
    }  
        
  /**
	* Gets the month-year from the month range selected by the user
	* 
	* Used to set the context with a valid limit to query data.     
	*	
	* 	
	* @param integer the number of month to go back to
	* @return string the month-year string
	* @access public
	*/
  public function setLimitMonth($range = DEFAULTMONTHRANGE, $overrideContext = false) {
    global $context;
    // check the context if we have the limit month saved from there
    if($context->limitMonth != null && $overrideContext != true) {
      $this->limitMonth = $context->limitMonth;
      class_debug::addMsg(__FILE__, 'Date range is '.$this->limitMonth.' from context', DEBUGINFO);
      return;
      }
    else {
      // if it is specific year given, then we do not do anything
      if(strlen($range) == 4) {
        $year = $range;
        $context->setlimitMonth($range, $year);
        class_debug::addMsg(__FILE__, 'Date range is year '.$range, DEBUGINFO);
        }
      else {
        class_debug::addMsg(__FILE__, 'getting the month-day for range: '.$range, DEBUGDEBUG);
        // if not we get it from DB from the default range
        $this->db->addFields('month');
        $this->db->addLimits(0, $range);
        $this->db->select('v_distinct_month');
        $month = $this->db->fetchAllRows();
        if($month !== false && is_array($month) && count($month) > 0) {
          $month = $month[count($month)-1]['month'];
          // set the internal value
          $this->limitMonth = $month;
          // set the context
          $context->setlimitMonth($range, $month);
          class_debug::addMsg(__FILE__, 'Date range is '.$month.' for range '.$range, DEBUGINFO);
          }
        else {
          class_debug::addMsg(__FILE__, 'Got no date range for '.$range, DEBUGINFO);
          }
        }
      }
    // reset the gotData flag has data may have changed
    $this->gotData = false;
    }
    
  /**
	* format a given string to be a valid number with 2 digits 
	* 	
	* @param string the data to be formated
	* @return string the formated data
	* @access public
	*/
  public function formatNumber($string) {
    return sprintf("%.2f", $string);
    } // formatNumber()
  
  /**
	* Return the category ID from its name 
	* 	
	* @param string the category name
	* @return integer the category ID
	* @access public
	*/
  public function getCategoryId($name) {
    return $this->categoryNames[$name];
    }
  
  /**
	* Search all transaction for a given keyword 
	* 	
	* @param string the keyword to look for
	* @return array An associative array with the transactions details
	* @access public	
	*/
  public function search($keyWord) {
    $results = null;
    
    // set the fields to retreive
    $this->db->addFields('id');
    $this->db->addFields('amount');
    $this->db->addFields('debit');
    $this->db->addFields('payee');
    $this->db->addFields('memo');
    $this->db->addFields('operationDate');
    $this->db->addFields('category');
    $this->db->addFields('comments');
    
    $this->db->addWhere('memo', 'like', '%'.$keyWord.'%');
    $this->db->addWhere('payee', 'like', '%'.$keyWord.'%', 'OR');
    $this->db->addWhere('amount', 'like', '%'.$keyWord.'%', 'OR');
    
    // there are no limits yet on this...we lookup the entire DB which is not great
    // TODO: let's think abouts putting some limits there
    
    // run the query
    $this->db->select('v_items');
        
    while(($item = $this->db->fetchRow()) !== false) {
        $item['amount'] = (float)sprintf("%.2f", $item['amount']);
        $results[] = $item;
        }
    return $results;
    }
  
  public function getData() {
    if($this->gotData == true) return;
    $dataCounter = 0;
    // set the fields to retreive
    $this->db->addFields('total');
    $this->db->addFields('count');
    $this->db->addFields('month');
    $this->db->addFields('year');
    $this->db->addFields('debit');
    $this->db->addFields('category');
    $this->db->addFields('name');
    $this->db->addFields('color');
    $this->db->addFields('maxAmountPerMonth');
    // set any where constraint
    if($this->month != null && $this->year != null) {
      $this->db->addWhere('month', '=', $this->month);
      $this->db->addWhere('year', '=', $this->year);
      }
    //$this->db->addWhere('category', '!=', 1);
    // switch depending on nature of limit month : if year then we lookup only on that specific year, if range we lookup for the given month range
    if(strlen($this->limitMonth) == 4) {
      $this->db->addWhere('year', '=', $this->limitMonth);
      }
    else {
      $this->db->addWhere('operationDate', '>=', $this->limitMonth);
      }
    // run the query
    $this->db->select('v_total_amount');
    
    
    // get the data and build the internal arrays
    $this->stats['unfiledAmount'] = 0;
    $this->stats['unfiledCount'] = 0;
    
    while(($item = $this->db->fetchRow()) !== false) {
      $dataCounter ++;
      // retreive unfiled amount and item count
      if(true) {
        if($item['category'] == '1') {
          $this->stats['unfiledAmount'] += (float)sprintf("%.2f", $item['total']);
          $this->stats['unfiledCount'] += $item['count'];
          }
        // all available month/year
        $this->stats['dates'][$item['month'].'-'.$item['year']] = $item['month'].'-'.$item['year'];
        // all the categories
        $this->stats['categories'][$item['category']] = $item['name'];
        $this->stats['categoryColor'][$item['category']] = $item['color'];
        $this->stats['categoryMaxAmountPerMonth'][$item['category']] = $item['maxAmountPerMonth'];
        $this->categoryNames[$item['name']] = $item['category'];
        // preset the arrays
        if($item['debit'] == '1') {
          // total debits amount
          @$this->stats['totalDebits'] += $item['total'];
          // debit per dates and categories
          $this->stats['debitPerMonthPerCategory'][$item['month'].'-'.$item['year']][$item['category']] = $item['total'];
          // total debit per dates
          @$this->stats['debitTotalPerMonth'][$item['month'].'-'.$item['year']] += $item['total'];
          // total debit per category
          @$this->stats['debitTotalPerCategory'][$item['category']] += $item['total'];
          // total per categories per dates
          $this->stats['debitPerCategoryPerMonth'][$item['category']][$item['month'].'-'.$item['year']] = $item['total'];
          }
        else {
          // total credits amount
          @$this->stats['totalCredits'] += $item['total'];
          // debit per dates and categories
          $this->stats['creditPerMonthPerCategory'][$item['month'].'-'.$item['year']][$item['category']] = $item['total'];
          // total debit per dates
          @$this->stats['creditTotalPerMonth'][$item['month'].'-'.$item['year']] += $item['total'];
          // total debit per category
          @$this->stats['creditTotalPerCategory'][$item['category']] += $item['total'];
          // total per categories per dates
          $this->stats['creditPerCategoryPerMonth'][$item['category']][$item['month'].'-'.$item['year']] = $item['total'];
          }
        }
      }
    
    if($dataCounter>0) {
      // compute the percentages
      if(isset($this->stats['debitTotalPerCategory'])) {
        foreach($this->stats['debitTotalPerCategory'] as $category=>$total) {
          $this->stats['debitPctPerCategory'][$category] = self::formatNumber($total / $this->stats['totalDebits'] * 100);
          }
        }
      
      if(isset($this->stats['creditTotalPerCategory'])) {
        foreach($this->stats['creditTotalPerCategory'] as $category=>$total) {
          $this->stats['creditPctPerCategory'][$category] = self::formatNumber($total / $this->stats['totalCredits'] * 100);
          }
        }
    
      if(isset($this->stats['debitTotalPerMonth'])) {
        foreach($this->stats['debitTotalPerMonth'] as $month=>$total) {
          $this->stats['debitPctPerMonth'][$month] = self::formatNumber($total / $this->stats['totalDebits'] * 100);
          }
        }
      
      if(isset($this->stats['creditTotalPerMonth'])) {
        foreach($this->stats['creditTotalPerMonth'] as $month=>$total) {
          $this->stats['creditPctPerMonth'][$month] = self::formatNumber($total / $this->stats['totalCredits'] * 100);
          }
        }
      
      // do some ordering
      if(isset($this->stats['categories']) && is_array($this->stats['categories'])) {
        asort($this->stats['categories'], SORT_STRING);
        }
      }
    
    class_debug::addMsg(__FILE__, 'Got '.$dataCounter.' transactions', DEBUGINFO);
    // set the internal flag to tell if data are loaded or not
    $this->gotData = true;
    // set false if no data
    if($dataCounter == 0) $this->validData = false;
    // else set the number of transation returned
    else $this->validData = $dataCounter;
    } // getData()
  
  /**
	* Gives the category name from the category Id 
	*	
  * @param integer the category id
	* @return String the category name
	* @access public
	*/
  public function getCategoryName($id=null) {
    if($id == null) return 'null';
    if($id != 1 && !isset($this->stats['categories'][$id])) {
      $this->db->addFields('name');
      $this->db->addWhere('id', '=', $id);
      $count = $this->db->select('v_categories', true);
      $res = $this->db->fetchRow();
      return $res['name'];
      }
    if($id == 1) return $this->keys->text_Uncategorized;
    else return $this->stats['categories'][$id];
    }
  
  private function getTotalItemCount() {
    $totalItemCount = 0;
    $this->db->addFields('count(id)');
    $this->db->select('t_items');
    $totalItemCount = $this->db->fetchRow();
    global $context;
    if($totalItemCount != false) $context->addToContext('totalItemCount', $totalItemCount['count(id)']);
    else $context->addToContext('totalItemCount', '-1');
    }
    
  /**
	* Provide an instance of the current class 
	*	
	* Implementation of the singleton pattern
	*   		
	* @return object An instance of this class
	* @access public
	* @static	
	*/      
  public static function getInstance() {
    if(is_null(self::$_instance)) {
      self::$_instance = new self();
      }
    return self::$_instance;
    } // getInstance()
  

  /**
	* class constructor 
	*	
	* access to this class is private as to implement the singleton pattern
	*   	
	* @return void
	* @access private
	*/
  private function __construct() {
    global $context;
    // get a DB object
    $this->db = class_db::getInstance();
    // get the current month limit
    self::setLimitMonth($context->range);
    // get the total number of transations
    self::getTotalItemCount();
    // get the property keys object
    $this->keys = class_propertyKey::getInstance();
    } // __construct()
  }
?>